<?php
class services
{
    public static function insert_sitemap_meta()
    {
        //read posted data
        $sitemap_url = mysql_real_escape_string($_POST['sitemap_url']);
        $use_dl = mysql_real_escape_string($_POST['use_dl']);
        $title_prefix = mysql_real_escape_string($_POST['title_prefix']); //for getting clean title out of sitemap urls
        $title_suffix = mysql_real_escape_string($_POST['title_suffix']); //for getting clean title out of sitemap urls
        $type = $_POST['type'];


        $title_separator = $_POST['title_separator']; //to further breakdown title using explode
        $title_segment = $_POST['title_segment']; //the position of segment after executing explode

        $page_title_prefix = mysql_real_escape_string($_POST['page_title_prefix']); //for page title display purposes
        $page_title_suffix = mysql_real_escape_string($_POST['page_title_suffix']); //for page title display purposes
        $url_keywords = $_POST['url_keywords']; //extra keywords to append after udnurl

        $mode = $_POST["mode"]; //what to do with links


        $con = util::open_connection();

        //define type details if not done already
        mysql_query("INSERT IGNORE INTO type_meta (title_prefix, title_suffix, type, url_keywords)
              VALUES ('$page_title_prefix', '$page_title_suffix', '$type', '$url_keywords')"); //will only insert if not exists. It will not update.

        //create a table for that type if not done already. eg. sitemap_data_book
        mysql_query("
            CREATE TABLE IF NOT EXISTS `sitemap_data_$type`
            (
              `uid` int(10) unsigned NOT NULL auto_increment,
              `sitemap_meta_id` tinyint(4) NOT NULL,
              `udnurl` varchar(200) NOT NULL,
              `sourceurl` varchar(200) NOT NULL,
              `title` varchar(200) NOT NULL,
              `google` BOOL NOT NULL,
              PRIMARY KEY  (`uid`),
              UNIQUE KEY `udnurl` (`udnurl`),
			  FULLTEXT KEY `title` (`title`),
              KEY `sitemap_meta_id` (`sitemap_meta_id`)
            )
            ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;
        ");

        //create a script cache table for that type if not done already. eg. script_cache_book
        mysql_query("
            CREATE TABLE IF NOT EXISTS `script_cache_$type` (
			  `udnurl` varchar(200) NOT NULL,
			  `script_name` varchar(200) NOT NULL,
			  `compresed_content` blob NOT NULL,
			  PRIMARY KEY  (`udnurl`)
			) ENGINE=MyISAM DEFAULT CHARSET=utf8;
        ");

        if($sitemap_url == '')
            $sitemap_url = $crawl_root_url; //if using crawler

        //define metadata for this sitemap
        mysql_query("INSERT INTO sitemap_meta (
                    sitemap_url
                    ,use_dl
                    ,title_prefix
                    ,title_suffix
                    ,type
                    ,title_separator
                    ,title_segment)
              VALUES ('$sitemap_url'
                    , $use_dl
                    ,'$title_prefix'
                    ,'$title_suffix'
                    ,'$type'
                    ,'$title_separator'
                    ,'$title_segment'
                    )");


		$sitemap_meta_id = mysql_insert_id(); //get id from mysql
        
        //insert processing status record
        //if($sitemap_meta_id >= 0)
        //    mysql_query("INSERT IGNORE INTO process_status (sitemap_meta_id) VALUES ($sitemap_meta_id)");
        

        //if it is inserted
        if($sitemap_meta_id >= 0)
        {
            $filename = 'sitemaps/'.util::get_filename_from_url($sitemap_url);
            set_time_limit(600);

            //if compressed, uncompress it
            if(strpos($sitemap_url,'.gz') > -1)
                util::uncompressFile($sitemap_url, $filename);
            else //simple download
                util::downloadFile($sitemap_url, $filename);

            services::traverse_sitemap($sitemap_meta_id, $filename, $title_prefix, $title_suffix, $title_separator, $title_segment, $type, $mode);
        }
        else
        {
            echo 'record is already inserted';
        }
        util::close_connection($con);

    }

    public static function traverse_sitemap($sitemap_meta_id, $filename, $title_prefix, $title_suffix, $title_separator, $title_segment, $type, $mode)
    {
        set_time_limit(0);//unlimited time

        if($mode == "write" || $mode == "write_google")
        {
            mysql_query("DELETE FROM sitemap_data_$type;");
            mysql_query("DELETE FROM sitemap_cache_$type;");
        }
        $total = 0;
        $cnt = 0;
        $file = fopen($filename, "r") or exit("Unable to open file!");
        fgets($file);//xml
        $sitemap_type = fgets($file);
        if(strpos($sitemap_type, "sitemapindex") !== FALSE)
        {
            while(!feof($file))
            {
                $line = fgets($file); //<url><loc>URL</loc></url>
                if(strpos($line, "<loc>") !== FALSE)
                {
                    $pcs = explode("<loc>",$line);
                    $pcs = explode("</loc>", $pcs[1]);
                    $line = $pcs[0];

                    $filename = 'sitemaps/'.util::get_filename_from_url($line); //filename for individual sitemaps in index
                    if(!file_exists($filename)) //only download if not already done, otherwise use existing sitemap file
                        util::downloadFile($line, $filename);

                    $sitemap_file = fopen($filename, "r");
                    while(!feof($file))
                    {
                        $line = fgets($file); //<url><loc>URL</loc></url>
                        if(strpos($line, "<url><loc>") !== FALSE)
                        {
                            $pcs = explode("<loc>",$line);
                            $pcs = explode("</loc>", $line);
                            $line = $pcs[0];

                            services::import_sitemap($sitemap_meta_id, $line, $title_prefix, $title_suffix, $title_separator, $title_segment, $type, $mode);
                            $total++;

                        }
                    }
                    fclose($sitemap_file);
                    $cnt++;
                }
            }
        }
        else
        {


            $cnt=1;
            //Output a line of the file until the end is reached
            while(!feof($file))
            {
                $line = fgets($file); //<url><loc>URL</loc></url>
                if(strpos($line, "<loc>") !== FALSE)
                {
                    $pcs = explode("<loc>",$line);
                    $pcs = explode("</loc>", $pcs[1]);
                    $line = $pcs[0];

                    services::import_sitemap($sitemap_meta_id, $line, $title_prefix, $title_suffix, $title_separator, $title_segment, $type, $mode);
                    $total++;
                }
            }
            echo $cnt.' sitemaps have been traversed in sitemap_data. ';
            echo $total.' records have been imported from '.$cnt.' sitemaps.';
        }

        fclose($file);
    }

    public static function import_sitemap($sitemap_meta_id, $line, $title_prefix, $title_suffix, $title_separator, $title_segment, $type, $mode)
    {
        $sourceurl=$line;
        $title = $sourceurl; //initially

        if($title_prefix != '')
        {
            $params = explode($title_prefix, $sourceurl);
            $title = $params[1];
        }
        if($title_suffix != '')
        {
            $params = explode($title_suffix, $title);
            $title = $params[0];
        }
        if($title_separator != '' && $title_segment != '')
        {
            $pcs = explode($title_separator,$title);
            if(strpos($title_segment,",") !== false)
            {
                $title = "";
                $segments = explode(",", $title_segment);
                $i = 0;
                foreach($segments as $segment_index)//multiple segments make the title
                {
                    $segment_index = intval($segment_index);
                    $title.="-".$pcs[$segment_index];
                }
                $title = substr($title, 1);
            }
            else
                $title = $pcs[$title_segment];
        }

        $title = urldecode($title);
        $title = util::clean_url($title); //eliminates any accented characters or symbols
        $udnurl = "r/$type/$title";

        services::insert_sitemap_data( $type, $sitemap_meta_id, $udnurl, $sourceurl, $title, $mode);
    }

    public static function get_sitemap_data($type, $udnurl)
    {
        $con = util::open_connection();

        $udnurl = mysql_real_escape_string($udnurl);
        $sitemap_data = null;
        $result = mysql_query(" SELECT * FROM sitemap_data_$type
                                LEFT JOIN sitemap_meta on sitemap_data_$type.sitemap_meta_id = sitemap_meta.sitemap_meta_id
                                LEFT JOIN type_meta on sitemap_meta.type = type_meta.type
                                WHERE sitemap_data_$type.udnurl = '$udnurl'");

        while ($row = mysql_fetch_array($result))
        {
            $row['title'] = util::make_title($row['title']);
            $sitemap_data = $row;

            //TODO: Fetch Missing Data Here

        }

        util::close_connection($con);
        return $sitemap_data;
    }

    public static function browse_sitemap_data($type, $pagesize, $page, $term)
    {
        $con = util::open_connection();

        $total = 0;//get total records for the given type ---- WARNING:THIS MUST BE STORED AS STATIC VALUE TO IMPROVE PERFORMANCE
        $result = mysql_query("   SELECT TABLE_ROWS FROM information_schema.tables
                        WHERE TABLE_SCHEMA = 'udnfind'
                        AND TABLE_NAME = 'sitemap_data_$type';"); //estimated for performance
        while ($row = mysql_fetch_array($result))
        {
            $total = $row[0];
        }
		$keywords ="";
		$result = mysql_query("SELECT * FROM type_meta WHERE type = '$type'");
		while ($row = mysql_fetch_array($result))
		{
			$keywords = $row['url_keywords'];
		}

        $letter = null;
        if(strpos($page,'-') !== false) //there is a letter filter
        {
            $temp = $page;
            $page = substr($page,0, strpos($page,'-'));
            $letter = substr($temp, strpos($temp,'-') + 1);
        }

        $start = ($page - 1) * $pagesize;
        $end = $start + $pagesize;
        $type = mysql_real_escape_string($type);
        $sitemap_data = array();
        $where = "1 = 1";
		if($term)
        {

            /******************************** SPHINX SEARCH START *************************************/
            /*require_once('sphinxapi.php');
            //Sphinx
            $s = new SphinxClient;
            $s->setServer("127.0.0.1", 9312);
            $s->setMatchMode(SPH_MATCH_EXTENDED2);

            //Search Query
            if($type == "all")
                $result = $s->query("@title $term"); //using all indices
            else
                $result = $s->query("@title $term", $type); //using index of current type

            if ($result['total'] > 0)
            {
                $i=0;
                foreach ($result['matches'] as $uid => $otherStuff) {
                    //Get Column
                    $row = mysql_fetch_array( mysql_query("SELECT * FROM sitemap_data_$type WHERE uid=$uid") );
                    $row['type'] = util::make_title($type);
                    $row['title'] = util::make_title($row['title']);
                    $row['udnurl'] = $row['udnurl'].'/'.$keywords;
                    $sitemap_data[$i] = $row;
                    $i++;
                }
            }
            else
            {
                //echo 'No results found';
            }*/
            /******************************** SPHINX SEARCH END **************************************/
/*
            util::close_connection($con);

            $output['total'] = $total;
            $output['size'] = $pagesize;
            $output['page'] = $page;
            $output['letter'] = $letter;
            $output['from'] = $start;
            $output['to'] = $start + $end;
            $output['page'] = $page;
            $output['pages'] = $total / $pagesize;
            $output['data'] = $sitemap_data;

            return $output;*/

            $subterms = explode('-', $term);
            foreach($subterms as $subterm)
            {
                $where .= " and MATCH(title) AGAINST('$subterm')";
            }
			$pagesize = 20;
        }
		elseif(strpos(strtolower($_SERVER['HTTP_USER_AGENT']),'google') !== FALSE)//is it google crawler?
		{
			$where .= " AND google = 1";
		}
        elseif($letter)
        {
            if(strpos($letter,'~') !== false) //0~9
            {
                $where .= " AND ( 0 = 1 ";
                for($i=0;$i<10;$i++)
                    $where .= " OR udnurl LIKE 'r/$type/$i%' ";
                $where .=" ) ";
            }
            else
                $where .= " AND udnurl LIKE 'r/$type/$letter%'";

            $pagesize = $start.", ".$pagesize;
        }
		else
		{
			$where .= " AND uid > $start AND uid < $end";
		}

        $result = mysql_query(" SELECT * FROM sitemap_data_$type
                                WHERE
								$where
                                LIMIT  $pagesize"); //optimized for large data
        $i=0;
        while ($row = mysql_fetch_array($result))
        {
            $row['type'] = util::make_title($type);
            $row['title'] = util::make_title($row['title']);
            $row['udnurl'] = $row['udnurl'].'/'.$keywords;
            $sitemap_data[$i] = $row;
            $i++;
        }

        util::close_connection($con);

        $output['total'] = $total;
        $output['size'] = $pagesize;
        $output['page'] = $page;
        $output['letter'] = $letter;
        $output['from'] = $start;
        $output['to'] = $start + $end;
        $output['page'] = $page;
        $output['pages'] = $total / 20;
        $output['data'] = $sitemap_data;

        return $output;
    }

    public static function browse_types()
    {
        $con = util::open_connection();

        $domain = util::get_current_domain();

        $where = " WHERE domain = '$domain'";

        if($domain == "udnfind.com" || $domain == "udnfind.info") $where = ""; //udnfind has all content types

        $types = array();
        $result = mysql_query(" SELECT DISTINCT(type) FROM type_meta $where");
        $i=0;
        while ($row = mysql_fetch_array($result))
        {
            $types[$i] = $row;
            $types[$i]['type'] = util::make_title($row['type']);
            $types[$i]['title'] = $types[$i]['type'];
            $types[$i]['udnurl'] = 'b/'.$row['type'].'/1';
            $i++;
        }

        util::close_connection($con);
        $output['data'] = $types;

        return $output;
    }

	public static function insert_sitemap_data($type, $sitemap_meta_id, $udnurl, $sourceurl, $title, $mode)
	{
		$udnurl = mysql_real_escape_string($udnurl);
		$sourceurl = mysql_real_escape_string($sourceurl);
		$title = mysql_real_escape_string($title);

        $google = 1;
        if($mode == "update_google") $google = 1;

		if ($mode !== "update_google" && $mode !== "update")
		{
			mysql_query("INSERT IGNORE INTO sitemap_data_$type (sitemap_meta_id, udnurl, sourceurl, title, google)
			VALUES ($sitemap_meta_id, '$udnurl', '$sourceurl', '$title', $google)");
			//echo strpos($mode,'update');exit();
		}

		
		if ($mode == "update_google" || $mode == "update")
        {
			//echo "UPDATE sitemap_data_$type SET google = $google WHERE sourceurl like '$sourceurl%'";exit();
            mysql_query("UPDATE `sitemap_data_$type` SET `google` = $google WHERE `sourceurl` like '$sourceurl%'");
			//mysql_query("UPDATE sitemap_data_movies SET google = 0");
			//exit();
			//mysql_query("UPDATE sitemap_data_$type SET google = $google WHERE udnurl = '$udnurl'");
        }

	}
	
	public static function get_sitemap_index($content_type)
    {
        //generate sitemap_index.xml page
        $xml = '<?xml version="1.0" encoding="UTF-8"?>'."\n".'<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'."\n";

        include('config.php');
        $con = util::open_connection();

        //get total records ---- WARNING:THIS MUST BE STORED AS STATIC VALUE TO IMPROVE PERFORMANCE
        $total_urls = 0;
        $records = mysql_query(" SELECT count(uid) as total FROM sitemap_data_$content_type WHERE google = 1");
        while ($row = mysql_fetch_array($records))
        {
            $total_urls = $row['total'];
        }

        $sitemap_urls = 50000;
        $cycles = ceil($total_urls / $sitemap_urls);
        $current_cycle = 0;


        while($current_cycle < $cycles)
        {
            $url = 'http://'.$_SERVER['SERVER_NAME'].$folder_url."s/$content_type/".$current_cycle.'/sitemap.xml';
            $xml.="<sitemap><loc>$url</loc></sitemap>"."\n";
            $current_cycle++;
        }


        util::close_connection($con);
        $xml.='</sitemapindex>';
        return $xml;
    }

    public static function get_sitemap($content_type, $page)
    {
        include('config.php');
        $con = util::open_connection();
		
		$keywords ="";
		$result = mysql_query("SELECT * FROM type_meta WHERE type = '$content_type'");
		while ($row = mysql_fetch_array($result))
		{
			$keywords = $row['url_keywords'];
		}
		
        $sitemap_urls = 50000;
        $start = $sitemap_urls * $page;

        $result = mysql_query("SELECT udnurl FROM sitemap_data_$content_type WHERE google = 1 LIMIT $start, $sitemap_urls");
        $xml = '<?xml version="1.0" encoding="UTF-8"?>'."\n".'<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'."\n";

        while ($row = mysql_fetch_array($result)) //build single sitemap
        {
            $url = 'http://'.$_SERVER['SERVER_NAME'].$folder_url.$row['udnurl']."/$keywords";
            $url = htmlspecialchars_decode($url);
            $url = htmlspecialchars($url);
            $xml.="<url><loc>$url</loc></url>"."\n";
        }
        $xml.='</urlset>';
        util::close_connection($con);
        return $xml;
    }

    public static function get_sitemap_meta($sitemap_meta_id)
    {
        include('config.php');
        $con = util::open_connection();

        $result = mysql_query("SELECT * FROM sitemap_meta WHERE sitemap_meta_id = $sitemap_meta_id");
        $sitemap_meta = null;
        while ($row = mysql_fetch_array($result))
        {
            $sitemap_meta = $row;
        }
        util::close_connection($con);
        return $sitemap_meta;
    }

    public static function page_not_found()
    {
        echo 'wrong page ('.$_SERVER['REQUEST_URI_PATH'].')';
    }

    public static function include_form($formname)
    {
        include('forms/'.$formname.'.php');
    }
	
    public static function insert_content_xpath($post)
    {
        $con = util::open_connection();
        $sitemap_meta_id = $post['sitemap_meta_id'];
        $title = $post['title'];
        $type = $post['type'];
        $multi = $post['multi'];
        $xpath = $post['xpath'];
        $attribute = $post['attribute'];
        $asis = $post['asis'];
        $place_holder = $post['place_holder'];

        mysql_query("DELETE FROM `content_xpath` WHERE sitemap_meta_id = $sitemap_meta_id");

        foreach($title as $a => $b)
        {
            mysql_query("INSERT IGNORE INTO content_xpath (display_order, sitemap_meta_id, title, type, multi, xpath, attribute, asis, place_holder)
		      VALUES ($a, $sitemap_meta_id, '$title[$a]', '$type[$a]', '$multi[$a]', '$xpath[$a]', '$attribute[$a]', '$asis[$a]', '$place_holder[$a]')");
        }
        util::close_connection($con);
    }

    public static function get_content_xpath_as_post($sitemap_meta_id)
    {
        $con = util::open_connection();

        $result = mysql_query("select * FROM `content_xpath` WHERE sitemap_meta_id = $sitemap_meta_id order by place_holder, display_order");
        $a = 0;

        while ($row = mysql_fetch_array($result))
        {
      		$title[$a] = $row['title'];
            $type[$a] = $row['type'];
            $multi[$a] = $row['multi'];
            $xpath[$a] = $row['xpath'];
            $attribute[$a] = $row['attribute'];
            $asis[$a] = $row['asis'];
            $place_holder[$a] = $row['place_holder'];
            $a++;
        }

        $post['sitemap_meta_id'] = $sitemap_meta_id;
        $post['title'] = $title;
        $post['type'] = $type;
        $post['multi'] = $multi;
        $post['xpath'] = $xpath;
        $post['attribute'] = $attribute;
        $post['asis'] = $asis;
        $post['place_holder'] = $place_holder;


        util::close_connection($con);
        return $post;
    }

    public static function insert_script_cache($content_type, $udnurl, $script_name, $data)
    {
        $con = util::open_connection();
        $escaped_data = mysql_real_escape_string($data);
        mysql_query("INSERT INTO `script_cache_$content_type` (udnurl, script_name, compresed_content) values ('$udnurl', '$script_name', compress('$escaped_data'))");
        util::close_connection($con);
    }

    public static function get_script_cache($content_type, $udnurl, $script_name)
    {
        $con = util::open_connection();
        $data = null;

        $result = mysql_query("select UNCOMPRESS(compresed_content) as data FROM `script_cache_$content_type` where udnurl='$udnurl' and script_name = '$script_name'");

        while ($row = mysql_fetch_array($result))
        {

            $data = $row['data'];
        }
        util::close_connection($con);

        return $data;
    }
	
	public static function insert_log($udnurl, $operation, $content_type)
    {
        $con = util::open_connection();
		$url = $_SERVER['REQUEST_URI'];
		$user = "N"; //normal user
		if(strpos(strtolower($_SERVER['HTTP_USER_AGENT']),'google') !== FALSE)
			$user = "G"; //Google Crawler
		
        mysql_query("INSERT INTO `log` (udnurl, url, user, dt, operation, content_type) values ('$udnurl', '$url', '$user', NOW(), '$operation', '$content_type')");
        util::close_connection($con);
    }
	
	
	
	//Stats Functions
	public static function content_type_stats($user)
    {
        $con = util::open_connection();
        $data = null;		
		
        $result = mysql_query("SELECT content_type, COUNT( content_type ) as cnt FROM log WHERE operation = 'r' AND user = '$user' and dt >= curdate() GROUP BY content_type");


        util::close_connection($con);
        return $result;
    }

	
	
}

?>